In SAS^® 9.1, using BULKLOAD=yes to insert new rows into an existing DB2 table might lead to unexpected results or to an S0C4 abend.

Unexpected or Incorrect Results Occur

Unexpected results occur when the input data set has the same number of columns as the existing DB2 table, but the order of the columns is different. If the data types of the columns in the same position are compatible, incorrect data is loaded for the columns that are in the wrong position. The data is added in the order in which the columns appear in the SAS data set rather than by matching variable names. If the data types of the columns are different, an error occurs.

So, if the DB2 table had three numeric variables called COL1, COL2, and COL3, in that order, and the SAS data set had the same three numeric variables but instead in the order COL3, COL2, COL1, the wrong values would be inserted into COL1 and COL3. COL1 would get the values from the variable COL3, whereas COL3 would get its values from the variable COL1. There would be no problem with COL2 because it is the second variable in both.

An Abend Occurs

If the SAS data set has fewer columns than the DB2 table, SAS fails with an S0C4 abend. The circumvention for any table except one that contains an identity column or one in which the DB2 column is defined as NOT NULL or NOT NULL WITH DEFAULT is to define the same number of columns in the SAS data set as what exists in the DB2 table. For the tables with identity columns or NOT NULL columns, bulk loading cannot be used. A different approach must be used to insert the rows. Remember that the order of the columns must match, in order to avoid the first issue.

If the SAS data set has more columns than the DB2 table, SAS also terminates with an S0C4 abend. The circumvention is to use a DROP= or KEEP= data set option for the SAS data set, keeping only those columns that are needed. Remember that the order of the columns must match to avoid the first problem.

Hot Fixes and Workarounds

Click the Hot Fix tab in this note to access the hot fixes for these issues.

After you apply the hot fix, the circumventions for the first issue and for the second issue (except in the case of an identity column or where a field is defined as not null) are no longer needed. However, if your SAS data set has more variables than the DB2 table, you still need to use the DROP= or KEEP= data set option. Otherwise, the following errors, notes, and warning occur:

ERROR: The number of columns of the input dataset exceeds that of the target table.
       Drop the column/s in excess before running the job again.
NOTE: The data step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set DB2LIB.TEST may be incomplete.  When this step was
         stopped there were 0 observations and 4 variables.
ERROR: Error prior to running the load utility. See previous errors messages for the step.

Operating System and Release Information

A fix for SAS 9.1 (9.1 TS1M0) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :

A fix for this issue for SAS/ACCESS Interface to DB2 9.3_M2 is available at: